#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project : bigDataProject 
@File    : sqlite_control.py
@IDE     : PyCharm 
@Author  : Mario
@Date    : 2022/11/10 14:36 
@Describe: SQLite3封装
"""
import sqlite3
from typing import Text, Any

from config.setting import ensure_path_sep
from utils.log.log_control import LogHandler

DB_FILE_PATH = ensure_path_sep("\\utils\\sqlite\\db_file\\")


class SqliteControl:
    def __init__(self, db_name: Text) -> None:
        """
        构造函数
        :param db_name: 需要操作的数据库文件
        """
        self.conn = sqlite3.connect(DB_FILE_PATH + db_name)
        self.cursor = self.conn.cursor()
        self.logger = LogHandler().get_logger()
        self.logger.debug("已连接{}数据库~".format(db_name))

    def __del__(self) -> None:
        try:
            self.cursor.close()
            self.conn.close()
            self.logger.debug("已关闭sqlite数据库链接~")
        except AttributeError as error:
            self.logger.warning("数据库关闭失败，失败原因:{}".format(error))

    def create_table(self, create_sql: Text) -> None:
        """
        创建表
        :param create_sql: 建表SQL
        :return: None
        """
        try:
            self.logger.debug("创建表成功~SQL:[{}]".format(create_sql))
            self.cursor.execute(create_sql)
        except AttributeError as error:
            self.logger.error("创建表失败,SQL:[{}],失败原因:{}".format(sql, error))

    def query_data(self, select_sql: Text) -> Any:
        """
        查询数据
        :param select_sql: 查询语句
        :return:
        """
        try:
            self.logger.debug("正在执行查询数据操作~查询语句:{}".format(select_sql))
            self.cursor.execute(select_sql)
            data = self.cursor.fetchall()
            self.logger.debug("查询数据成功，数据:{}".format(data))
            return data
        except AttributeError as error:
            self.logger.error("查询数据失败,查询语句:{}，失败原因:{}".format(select_sql, error))

    def db_data_operate(self, operate_sql: Text) -> None:
        """
        数据库操作（增删改）
        :param operate_sql: 增删改语句
        :return:
        """
        try:
            rows = self.cursor.execute(operate_sql)
            self.conn.commit()
            self.logger.info("已执行数据库操作。操作SQL:[{}],影响行数:[{}]".format(sql, rows))
        except AttributeError as error:
            self.logger.error("数据库操作失败，正在进行回滚操作~操作SQL:[{}],错误信息:[{}]".format(sql, error))
            # 如果事务异常，则回滚数据
            self.conn.rollback()
            raise


if __name__ == '__main__':
    sql = "select * from overseas_upload_order"
    try:
        test = SqliteControl('support.db')
        table = test.query_data(sql)
        print(type(table))
    except Exception as e:
        print(e)
